using System;
using System.Data;
using System.Text;
using Toenda.Rails.DataContainer;
using Toenda.TFL.Data;

namespace Toenda.Rails {
	namespace Data {
		/// <summary>
		/// Class DBClient based on DBSystem
		/// </summary>
		public class DBClient {
			protected DAL DBBaseClass = null;
			protected DALSettings Settings = null;

			/// <summary>
			/// Default constructor
			/// </summary>
			public DBClient() {
				Settings = new DALSettings();

				Settings.Database = DBInfo.Current.Database;
				//Settings.DatabaseUser = DBInfo.Current.DatabaseUser;
				Settings.PrimaryKeyName = DBInfo.Current.IDName;
				Settings.Password = DBInfo.Current.Password;
				Settings.Server = DBInfo.Current.Server;
				//Settings.TrustCn = DBInfo.Current.TrustCn;
				Settings.User = DBInfo.Current.Username;
				Settings.DatabaseEngine = DatabaseEngine.MSSQL2005;

				DBBaseClass = new DAL(Settings);
			}

			/// <summary>
			/// Update a dataset in a specific table
			/// </summary>
			/// <param name="data"></param>
			/// <param name="table"></param>
			/// <returns></returns>
			protected int UpdateValue(DataSet data, string table) {
				return DBBaseClass.ExecuteUpdate(data, table);
			}

			/// <summary>
			/// Get user id by a current session id
			/// </summary>
			/// <param name="Session"></param>
			/// <returns></returns>
			protected string GetUserIDBySessionID(string Session) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT * FROM [dbo].[tbl_session]");
				str.Append(" WHERE [pkid] = '" + Session + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());
				string strReturn = "";

				if(dsData.Tables[0].Rows.Count > 0) {
					DataTableReader oDR = dsData.CreateDataReader();
					oDR.Read();

					strReturn = oDR["user_pkid"].ToString();
				}

				return strReturn;
			}

			/// <summary>
			/// Get a user
			/// </summary>
			/// <param name="Username"></param>
			/// <returns></returns>
			protected DataSet GetUserByUsername(string Username) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_user]");
				str.Append(" INNER JOIN [dbo].[tbl_usergroup]");
				str.Append(" ON [tbl_user].[group_pkid] = [tbl_usergroup].[pkid]");
				str.Append(" WHERE [tbl_user].[username] = '" + Username + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a user
			/// </summary>
			/// <param name="UID"></param>
			/// <returns></returns>
			protected DataSet GetUserWithoutGroup(string UID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT * FROM [dbo].[tbl_user]");
				str.Append(" WHERE [tbl_user].[pkid] = '" + UID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a user
			/// </summary>
			/// <param name="UID"></param>
			/// <returns></returns>
			protected DataSet GetUser(string UID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_user]");
				str.Append(" INNER JOIN [dbo].[tbl_usergroup]");
				str.Append(" ON [tbl_user].[group_pkid] = [tbl_usergroup].[pkid]");
				str.Append(" WHERE [tbl_user].[pkid] = '" + UID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a user
			/// </summary>
			/// <param name="UID"></param>
			/// <param name="Create"></param>
			/// <returns></returns>
			protected DataSet GetUser(string UID, bool Create) {
				StringBuilder strSQLString = new StringBuilder();
				DataSet dsData = new DataSet();

				if(Create) {
					strSQLString.Append("SELECT *");
					strSQLString.Append(" FROM [dbo].[tbl_user]");
					strSQLString.Append(" INNER JOIN [dbo].[tbl_usergroup]");
					strSQLString.Append(" ON [tbl_user].[group_pkid] = [tbl_usergroup].[pkid]");
					strSQLString.Append(" WHERE [tbl_user].[pkid] = '" + UID + "'");

					dsData = DBBaseClass.ExecuteQuery(strSQLString.ToString());

					if(dsData.Tables[0].Rows.Count == 0) {
						strSQLString.Remove(0, strSQLString.Length);
						strSQLString.Append("INSERT INTO [dbo].[tbl_user]\n");
						strSQLString.Append("Select TOP 1 '" + UID + "', pkid, '','','','','','','','','',1,0,50,25,25,100,0 FROM [dbo].[tbl_usergroup] WHERE description = 'User'");

						DBBaseClass.ExecuteNonQuery(strSQLString.ToString());

						strSQLString.Remove(0, strSQLString.Length);
						strSQLString.Append("SELECT *");
						strSQLString.Append(" FROM [dbo].[tbl_user]");
						strSQLString.Append(" INNER JOIN [dbo].[tbl_usergroup]");
						strSQLString.Append(" ON [tbl_user].[group_pkid] = [tbl_usergroup].[pkid]");
						strSQLString.Append(" WHERE [tbl_user].[pkid] = '" + UID + "'");

						dsData = DBBaseClass.ExecuteQuery(strSQLString.ToString());
					}
				}
				else {
					strSQLString.Append("SELECT *");
					strSQLString.Append(" FROM [dbo].[tbl_user]");
					//strSQLString.Append(" INNER JOIN [dbo].[tbl_usergroup]");
					//strSQLString.Append(" ON [tbl_user].[group_pkid] = [tbl_usergroup].[pkid]");
					strSQLString.Append(" WHERE [tbl_user].[pkid] = '" + UID + "'");

					dsData = DBBaseClass.ExecuteQuery(strSQLString.ToString());
				}

				return dsData;
			}

			/// <summary>
			/// Get user job list
			/// </summary>
			/// <param name="UID"></param>
			/// <returns></returns>
			protected DataSet GetCompanyJobList(string UID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_jobs]");
				str.Append(" WHERE [tbl_jobs].[company_pkid] = '" + UID + "'");
				str.Append(" ORDER BY [creation_date] DESC");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a company
			/// </summary>
			/// <param name="UID"></param>
			/// <returns></returns>
			protected DataSet GetCompanyData(string UID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_company]");
				str.Append(" WHERE [pkid] = '" + UID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a company with its job list
			/// </summary>
			/// <param name="UID"></param>
			/// <returns></returns>
			protected DataSet GetCompanyWithJob(string UID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_company] AS com");
				str.Append(" INNER JOIN [dbo].[tbl_jobs] AS jobs");
				str.Append(" ON jobs.company_pkid = com.pkid");
				str.Append(" WHERE com.[pkid] = '" + UID + "'");
				str.Append(" ORDER BY jobs.[creation_date] DESC");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get the complete job list for a state
			/// </summary>
			/// <param name="State"></param>
			/// <param name="la"></param>
			/// <returns></returns>
			protected DataSet GetStateJobList(string State, ListAttribute la) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT jobs.*, com.title AS comTitle, com.street AS comStreet, com.state AS comState, com.zipcode AS comZipcode, com.city AS comCity, stc.content as comCountry, com.online_apply AS comOnlineApply, com.job_email AS comJobEmail, com.job_email_subject AS comJobEmailSubject, com.email AS comEmail, com.web AS comWeb");
				str.Append(" FROM [dbo].[tbl_jobs] AS jobs");
				str.Append(" INNER JOIN [dbo].[tbl_company] AS com");
				str.Append(" ON jobs.company_pkid = com.pkid");
				str.Append(" LEFT JOIN [dbo].[tbl_static] AS stc");
				str.Append(" ON stc.value = com.country");
				str.Append(" WHERE com.state = '" + State + "'");
				str.Append(" AND com.enabled = 1");
				str.Append(" AND jobs.enabled = 1");
				str.Append(" AND (jobs.[publish_date] < getdate())");
				str.Append(" AND ((dateadd(w, jobs.[term], jobs.[publish_date])) > getdate() OR jobs.[term] = 0)");

				switch(la) {
					case ListAttribute.AllEnabled:
						str.Append(" AND (jobs.[enabled] = 1)");
						break;

					case ListAttribute.AllDisabled:
						str.Append(" AND (jobs.[enabled] = 0)");
						break;

					case ListAttribute.All:
						str.Append(" ");
						break;
				}
				
				str.Append(" ORDER BY [creation_date] DESC");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get the complete job list for a state
			/// </summary>
			/// <param name="State"></param>
			/// <param name="Category"></param>
			/// <param name="la"></param>
			/// <returns></returns>
			protected DataSet GetStateJobList(string State, string Category, ListAttribute la) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT jobs.*, com.title AS comTitle, com.street AS comStreet, com.state AS comState, com.zipcode AS comZipcode, com.city AS comCity, stc.content as comCountry, com.online_apply AS comOnlineApply, com.job_email AS comJobEmail, com.job_email_subject AS comJobEmailSubject, com.email AS comEmail, com.web AS comWeb");
				str.Append(" FROM [dbo].[tbl_jobs] AS jobs");
				str.Append(" INNER JOIN [dbo].[tbl_company] AS com");
				str.Append(" ON jobs.company_pkid = com.pkid");
				str.Append(" LEFT JOIN [dbo].[tbl_static] AS stc");
				str.Append(" ON stc.value = com.country");
				str.Append(" WHERE com.state = '" + State + "'");
				str.Append(" AND jobs.category = '" + Category + "'");
				str.Append(" AND com.enabled = 1");
				str.Append(" AND jobs.enabled = 1");
				str.Append(" AND (jobs.[publish_date] < getdate())");
				str.Append(" AND ((dateadd(w, jobs.[term], jobs.[publish_date])) > getdate() OR jobs.[term] = 0)");

				switch(la) {
					case ListAttribute.AllEnabled:
						str.Append(" AND (jobs.[enabled] = 1)");
						break;

					case ListAttribute.AllDisabled:
						str.Append(" AND (jobs.[enabled] = 0)");
						break;

					case ListAttribute.All:
						str.Append(" ");
						break;
				}

				str.Append(" ORDER BY [creation_date] DESC");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get the complete job list
			/// </summary>
			/// <returns></returns>
			protected DataSet GetCompleteJobList() {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_jobs]");
				str.Append(" ORDER BY [creation_date] DESC");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a single job without any additional data
			/// </summary>
			/// <param name="ID"></param>
			/// <returns></returns>
			protected DataSet GetSingleJob(string ID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_jobs]");
				str.Append(" WHERE [pkid] = '" + ID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get a single job without any additional data
			/// </summary>
			/// <param name="ID"></param>
			/// <returns></returns>
			protected DataSet GetSingleJobApplication(string ID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_job_application]");
				str.Append(" WHERE [pkid] = '" + ID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get statics
			/// </summary>
			/// <param name="ID"></param>
			/// <returns></returns>
			protected DataSet GetJobData(string ID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT jobs.*, com.title AS comTitle, com.street AS comStreet, com.state AS comState, com.zipcode AS comZipcode, com.city AS comCity, stc.content AS comCountry, com.online_apply AS comOnlineApply, com.job_email AS comJobEmail, com.job_email_subject AS comJobEmailSubject, com.email AS comEmail, com.web AS comWeb");
				str.Append(" FROM [dbo].[tbl_jobs] AS jobs");
				str.Append(" INNER JOIN [dbo].[tbl_company] AS com");
				str.Append(" ON jobs.company_pkid = com.pkid");
				str.Append(" LEFT JOIN [dbo].[tbl_static] AS stc");
				str.Append(" ON stc.value = com.country");
				str.Append(" WHERE jobs.[pkid] = '" + ID + "'");
				str.Append(" AND (jobs.[publish_date] < getdate())");
				str.Append(" AND ((dateadd(w, jobs.[term], jobs.[publish_date])) > getdate() OR jobs.[term] = 0)");
				str.Append(" AND (jobs.[enabled] = 1)");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get statics
			/// </summary>
			/// <param name="ID"></param>
			/// <returns></returns>
			protected DataSet GetJobDataForEditing(string ID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT jobs.*, com.title AS comTitle, com.street AS comStreet, com.state AS comState, com.zipcode AS comZipcode, com.city AS comCity, stc.content AS comCountry, com.online_apply AS comOnlineApply, com.job_email AS comJobEmail, com.job_email_subject AS comJobEmailSubject, com.email AS comEmail, com.web AS comWeb");
				str.Append(" FROM [dbo].[tbl_jobs] AS jobs");
				str.Append(" INNER JOIN [dbo].[tbl_company] AS com");
				str.Append(" ON jobs.company_pkid = com.pkid");
				str.Append(" LEFT JOIN [dbo].[tbl_static] AS stc");
				str.Append(" ON stc.value = com.country");
				str.Append(" WHERE jobs.[pkid] = '" + ID + "'");
				str.Append(" AND (jobs.[publish_date] < getdate())");
				str.Append(" AND ((dateadd(w, jobs.[term], jobs.[publish_date])) > getdate() OR jobs.[term] = 0)");
				//str.Append(" AND (jobs.[enabled] = 1)");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get statics
			/// </summary>
			/// <param name="ID"></param>
			/// <returns></returns>
			protected DataSet GetLayoutData(string ID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_layout]");
				str.Append(" WHERE [id] = '" + ID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get statics
			/// </summary>
			/// <param name="Tag"></param>
			/// <param name="osb"></param>
			/// <returns></returns>
			protected DataSet GetStatics(string Tag, OrderStaticsBy osb) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_static]");
				str.Append(" WHERE [tag] = '" + Tag + "'");

				switch(osb) {
					case OrderStaticsBy.Undefined:
						str.Append(" ORDER BY [value] ASC");
						break;

					case OrderStaticsBy.ValueASC:
						str.Append(" ORDER BY [value] ASC");
						break;

					case OrderStaticsBy.ValueDESC:
						str.Append(" ORDER BY [value] DESC");
						break;

					case OrderStaticsBy.ContentASC:
						str.Append(" ORDER BY [content] ASC");
						break;

					case OrderStaticsBy.ContentDESC:
						str.Append(" ORDER BY [content] DESC");
						break;
				}

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get statics
			/// </summary>
			/// <param name="Tag"></param>
			/// <param name="Value"></param>
			/// <returns></returns>
			protected DataSet GetStatic(string Tag, string Value) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_static]");
				str.Append(" WHERE [tag] = '" + Tag + "'");
				str.Append(" AND [value] = '" + Value + "'");
				str.Append(" ORDER BY [value] ASC");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get user settings
			/// </summary>
			/// <param name="ID"></param>
			/// <returns></returns>
			protected DataSet GetUserSettings(string ID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_user_settings]");
				str.Append(" WHERE pkid = '" + ID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}

			/// <summary>
			/// Get user settings by userid
			/// </summary>
			/// <param name="userID"></param>
			/// <returns></returns>
			protected DataSet GetUserSettingsByUserID(string userID) {
				StringBuilder str = new StringBuilder();

				str.Append("SELECT *");
				str.Append(" FROM [dbo].[tbl_user_settings]");
				str.Append(" WHERE user_pkid = '" + userID + "'");

				DataSet dsData = DBBaseClass.ExecuteQuery(str.ToString());

				return dsData;
			}
		}
	}
}
